set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* *******************************************************************
** DATE :
** Writer :
** Memo :
******************************************************************** */
CREATE PROCEDURE [dbo].[TSP_UserCard_GetListManager]
(
	@IsTransaction	tinyint			= 0
,	@ErrorCode		int				= 0		OUTPUT
,	@ErrorMessage	nvarchar( 1000 )= ''	OUTPUT

,	@n4UserNo	int	= NULL
,	@CodeStatus tinyint
,	@strCardNo	varchar(100) = NULL
,	@PageNo	int	= 1	
,	@PageSize	tinyint	= 10	
,	@CntRow	int	= NULL	OUTPUT
,	@CntTotal	int	= NULL	OUTPUT
)
as
Set NOCOUNT ON

-- Frk Default Value
DECLARE @FrkDateNow DateTime
DECLARE @FrkRowCount int
DECLARE @FrkTranCount int
DECLARE @FrkErrorCode int
DECLARE @FrkErrorMessage nvarchar( 1000 )
Set @FrkDateNow = GetDate()
set @FrkTranCount = @@TRANCOUNT
-- Frk Check


If ( @IsTransaction = 0 )
Begin
	If ( @FrkTranCount < 1 )
	Begin
		Set @FrkErrorCode = -1
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Else If ( @FrkTranCount > 1 )
	Begin
		Set @FrkErrorCode = -2
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
End
Else
Begin
	If ( @FrkTranCount <> 0 )
	Begin
		Set @FrkErrorCode = -3
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Begin Tran
End

-- Implementation

DECLARE @SkipPage INT
SET @SkipPage = ( ( @PageNo - 1 ) * @PageSize )


IF ( @n4UserNo IS NULL )
BEGIN
	IF ( @strCardNo IS NULL )
	BEGIN
		SELECT
			@CntTotal = COUNT( * )
		FROM
			dbo.TB_UserCard with ( nolock )
		WHERE
			CodeStatus = @CodeStatus

		IF ( @PageNo = 1 )
		BEGIN
			SELECT	Top( @PageSize )
				C.n4UserCardNo
			,	C.n4UserNo
			,	C.strCardNo
			,	C.CodeStatus
			,	C.dtCreateDate
			,	C.dtModifyDate
			,	C.strModifyManagerName
			,	C.strDesc
			,	C.dtRemoveDate
			,	U.strUserID
			,	U.strUserName
			FROM
				dbo.TB_UserCard AS C with ( nolock )
			LEFT OUTER JOIN
				dbo.TB_User AS U with ( nolock )
			ON	U.n4UserNo = C.n4UserNo
			WHERE
				C.CodeStatus = @CodeStatus
			ORDER BY
				C.CodeStatus DESC, C.n4UserCardNo DESC
		END
		ELSE
		BEGIN
			SELECT	Top( @PageSize )
				C.n4UserCardNo
			,	C.n4UserNo
			,	C.strCardNo
			,	C.CodeStatus
			,	C.dtCreateDate
			,	C.dtModifyDate
			,	C.strModifyManagerName
			,	C.strDesc
			,	C.dtRemoveDate
			,	U.strUserID
			,	U.strUserName
			FROM
			(
				SELECT
					n4UserCardNo
				,	n4UserNo
				,	strCardNo
				,	CodeStatus
				,	dtCreateDate
				,	dtModifyDate
				,	strModifyManagerName
				,	strDesc
				,	dtRemoveDate
				,	ROW_NUMBER() OVER ( ORDER BY
					CodeStatus DESC, n4UserCardNo DESC
				) AS ROWNUM
				FROM
					dbo.TB_UserCard AS C with ( nolock )
				WHERE
					C.CodeStatus = @CodeStatus
			)	C
			LEFT OUTER JOIN
				dbo.TB_User AS U with ( nolock )
			ON	U.n4UserNo = C.n4UserNo
			WHERE
				ROWNUM > @SkipPage
			AND	ROWNUM <= @SkipPage + @PageSize
		END
	END
	ELSE
	BEGIN
		SELECT
			@CntTotal = COUNT( * )
		FROM
			dbo.TB_UserCard with ( nolock )
		WHERE
			CodeStatus = @CodeStatus
		AND	strCardNo LIKE @strCardNo + '%'

		IF ( @PageNo = 1 )
		BEGIN
			SELECT	Top( @PageSize )
				C.n4UserCardNo
			,	C.n4UserNo
			,	C.strCardNo
			,	C.CodeStatus
			,	C.dtCreateDate
			,	C.dtModifyDate
			,	C.strModifyManagerName
			,	C.strDesc
			,	C.dtRemoveDate
			,	U.strUserID
			,	U.strUserName
			FROM
				dbo.TB_UserCard AS C with ( nolock )
			LEFT OUTER JOIN
				dbo.TB_User AS U with ( nolock )
			ON	U.n4UserNo = C.n4UserNo
			WHERE
				C.CodeStatus = @CodeStatus
			AND	C.strCardNo LIKE @strCardNo + '%'
			ORDER BY
				C.strCardNo DESC, C.n4UserCardNo DESC
		END
		ELSE
		BEGIN
			SELECT	Top( @PageSize )
				C.n4UserCardNo
			,	C.n4UserNo
			,	C.strCardNo
			,	C.CodeStatus
			,	C.dtCreateDate
			,	C.dtModifyDate
			,	C.strModifyManagerName
			,	C.strDesc
			,	C.dtRemoveDate
			,	U.strUserID
			,	U.strUserName
			FROM
			(
				SELECT
					n4UserCardNo
				,	n4UserNo
				,	strCardNo
				,	CodeStatus
				,	dtCreateDate
				,	dtModifyDate
				,	strModifyManagerName
				,	strDesc
				,	dtRemoveDate
				,	ROW_NUMBER() OVER ( ORDER BY
					strCardNo, n4UserCardNo DESC
				) AS ROWNUM
				FROM
					dbo.TB_UserCard AS C with ( nolock )
				WHERE
					CodeStatus = @CodeStatus
				AND	strCardNo LIKE @strCardNo + '%'
			)	C
			LEFT OUTER JOIN
				dbo.TB_User AS U with ( nolock )
			ON	U.n4UserNo = C.n4UserNo
			WHERE
				ROWNUM > @SkipPage
			AND	ROWNUM <= @SkipPage + @PageSize
			ORDER BY
				C.strCardNo DESC, C.n4UserCardNo DESC
		END
	END
END
ELSE
BEGIN
	SELECT
		@CntTotal = COUNT( * )
	FROM
		dbo.TB_UserCard with ( nolock )
	WHERE
		n4UserNo = @n4UserNo
	AND	CodeStatus = @CodeStatus

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			C.n4UserCardNo
		,	C.n4UserNo
		,	C.strCardNo
		,	C.CodeStatus
		,	C.dtCreateDate
		,	C.dtModifyDate
		,	C.strModifyManagerName
		,	U.strUserID
		,	U.strUserName
		FROM
			dbo.TB_UserCard AS C with ( nolock )
		LEFT OUTER JOIN
			dbo.TB_User AS U with ( nolock )
		ON	U.n4UserNo = C.n4UserNo
		WHERE
			C.n4UserNo = @n4UserNo
		AND	C.CodeStatus = @CodeStatus
		ORDER BY
			C.n4UserNo DESC, C.n4UserCardNo DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			C.n4UserCardNo
		,	C.n4UserNo
		,	C.strCardNo
		,	C.CodeStatus
		,	C.dtCreateDate
		,	C.dtModifyDate
		,	C.strModifyManagerName
		,	U.strUserID
		,	U.strUserName
		FROM
		(
			SELECT
				n4UserCardNo
			,	n4UserNo
			,	strCardNo
			,	CodeStatus
			,	dtCreateDate
			,	dtModifyDate
			,	strModifyManagerName
			,	ROW_NUMBER() OVER ( ORDER BY
				n4UserNo DESC, n4UserCardNo DESC
			) AS ROWNUM
			FROM
				dbo.TB_UserCard AS C with ( nolock )
			WHERE
				n4UserNo = @n4UserNo
			AND	CodeStatus = @CodeStatus
		)	C
		LEFT OUTER JOIN
			dbo.TB_User AS U with ( nolock )
		ON	U.n4UserNo = C.n4UserNo
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			n4UserNo DESC, n4UserCardNo DESC
	END
END

-- Frk Error Check
SELECT
	@FrkErrorCode = @@ERROR
,	@FrkRowCount = @@RowCount

-- End

COMPLETE:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Commit Tran
		End
	End
	SET @ErrorCode = 0
	SET @ErrorMessage = ''
	return 0
ERROR:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Rollback Tran
		End
	End
	SET @ErrorCode = @FrkErrorCode
	SET @ErrorMessage = @FrkErrorMessage
	return @FrkErrorCode
